package hw3.sql;


import hw3.exception.StdErrReporterException;
import hw3.proxy.gui.QueryResultFrame;
import hw3.xhtml.XHTMLDocument;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import javax.swing.event.TableModelListener;
import javax.swing.table.TableModel;

import com.mysql.jdbc.PreparedStatement;

/**
 * This class is responsible for accessing a MySQL DB.
 * 
 * @author Adam Levi
 * @author Marina Skarbovsky
 *
 */
public class SQLDBManager
{
	/**
	 * creates a SQLDBManager object 
	 * 
	 * @param host The DB host name
	 * @param port The DB port number
	 * @param dbName The DB name at the host
	 * @param username The user name that will be used to connect to the DB
	 * @param password The user name that will be used to connect to the DB
	 */
	public SQLDBManager(
			String host,
			int port,
			String dBName,
			String username,
			String password
			)
	{
		m_DbURL = "jdbc:mysql://" + host + ":" + Integer.toString(port)+ "/" + dBName;
		m_UserName = username;
		m_Password = password;
		m_ConnectionValidationTimeout = 2;
	}
	
	/**
	 * terminates the connection to the server and closes the Statement variable.
	 */
	public void dispose() throws SQLException
	{
		closeStatement();
		closeConnection();
	}
	
	/**
	 * connects to the DB.
	 */
	public void connect() throws ClassNotFoundException, SQLException
	{
		m_Connection = null;
		boolean noError = false;
		try
		{ 
			// Load the Driver class. 
			Class.forName("com.mysql.jdbc.Driver"); 

			//Create the connection using the static getConnection method 
			m_Connection = DriverManager.getConnection (m_DbURL, m_UserName, m_Password);
			
			//Create a Statement class to execute the SQL statement 
			m_Statement = m_Connection.createStatement();
			
			noError = true;
			
		}
		finally
		{ 
			if (noError)
			{
				return;
			}
			if(m_Statement != null)
			{
				closeStatement();
			}
			if(m_Connection != null)
			{
				closeConnection();
			}
		} 
	}
	
	/**
	 * closes the m_Statement variable
	 */
	void closeStatement() throws SQLException
	{
		if(m_Statement != null)
		{
		    m_Statement.close();
		    m_Statement = null;
		}	
	}
	
	/**
	 * closes the connection to the DB.
	 */
	void closeConnection() throws SQLException
	{
		if(m_Connection != null)
		{
		     m_Connection.close();
		     m_Connection = null;
		}
	}
	
	/**
	 * Tests if a connection was made to the DB. If not the method will try to connect, 
	 * otherwise it will verify that the connection is still valid.
	 */
	protected void validateConnection() throws ClassNotFoundException, SQLException
	{
		if (m_Connection == null || !m_Connection.isValid(m_ConnectionValidationTimeout)) // validate time = 2 seconds
		{
			try { closeStatement(); }
			catch(Exception e) { } // no reason to report this error 
			connect();
		}	
	}
	
	/**
	 * updates the DB.
	 * @param update a SQL query string that will be executed on the DB.
	 * @return either (1) the row count for SQL Data Manipulation Language (DML) statements
	 * or (2) 0 for SQL statements that return nothing.
	 */
	protected int doUpdate(String update) throws SQLException, ClassNotFoundException
	{
		validateConnection();
		return m_Statement.executeUpdate(update);
	}
	
	/**
	 * Queries the DB.
	 * @param query an SQL query string 
	 * @return A result set object that is the result of the query
	 */
	protected ResultSet doQuery(String query) throws SQLException, ClassNotFoundException
	{
		validateConnection();
		m_CurrentPreparedStatement = (PreparedStatement) m_Connection.prepareStatement(query);
		ResultSet res = m_CurrentPreparedStatement.executeQuery();
		return res;
	}
	
	protected void afterQuery()
	{
		SQLDBManager.ClosePreparedStatement(m_CurrentPreparedStatement);
		m_CurrentPreparedStatement = null;
	}
	
	/**
	 * creates a new table in the DB.
	 * @param tableName The table's name
	 * @param columnsNames an array that holds all the columns' names.
	 * @param columnTypes  an array that holds all the columns' SQL types.
	 * @param primaryKeyColumnNumber the column number that will be used as primary key (optional)
	 * @param primaryKeyLength the key length (optional)
	 */
	protected void createTable(
			String tableName,
			String[] columnsNames,
			String[] columnTypes,
			Integer primaryKeyColumnNumber,
			Integer primaryKeyLength) throws SQLException, ClassNotFoundException
	{
		if (
				tableName == null || tableName.equals("") ||
				columnsNames == null || columnTypes == null ||
				columnsNames.length != columnTypes.length
			)
		{
			throw new IllegalArgumentException();
		}
		ArrayList<ColumnType> arList = new ArrayList<ColumnType>(columnsNames.length);
		for (int i = 0; i < columnsNames.length; i++)
		{
			ColumnType ct = new ColumnType();
			ct.name = columnsNames[i];
			ct.sqlTypeName = columnTypes[i];
			if (primaryKeyColumnNumber == i)
			{
				ct.primaryKey = true;
				ct.primaryKeyLength = primaryKeyLength;
			}
			arList.add(ct);
		}
		createTable(tableName, arList);
	}
	
	/**
	 * creates a new table in the DB.
	 * @param tableName the table's name
	 * @param columns a collection that holds the data (see ColumnType)
	 */
	protected void createTable(
			String tableName,
			Collection<ColumnType> columns) throws SQLException, ClassNotFoundException
	{
		if (tableName == null || tableName.equals("") || columns == null)
		{
			throw new IllegalArgumentException();
		}
		
		// building query
	    StringBuilder createTableString = new StringBuilder(1000);
	    String primaryKeyString = null;
	    createTableString.append("CREATE TABLE ");
	    createTableString.append(tableName);
	    createTableString.append(" (");
	    int i = 0;
	    for (ColumnType ct : columns)
	    {
	    	createTableString.append(ct.name);
	    	createTableString.append(" ");
	    	createTableString.append(ct.sqlTypeName);
	    	if (ct.primaryKey)
	    	{
	    		primaryKeyString = 
	    			",PRIMARY KEY (" + ct.name + "(" + 
	    			ct.primaryKeyLength.toString() +"))" ;
	    	}
	    	i++;
	    	if (i < columns.size())
	    	{
	    		createTableString.append(",");
	    	}
	    }
	    if (primaryKeyString != null)
	    {
	    	createTableString.append(primaryKeyString);
	    }
	    createTableString.append(") ENGINE=InnoDB");
	    
	    //executing
		doUpdate(createTableString.toString());
	}
	
	/**
	 * deletes a table
	 * @param tableName The table's name 
	 */
	protected void dropTable(String tableName) throws SQLException, ClassNotFoundException
	{
		String query = "DROP TABLE " + tableName + ";";
		doUpdate(query);
	}
	
	/**
	 * clears a table
	 * @param tableName The table's name 
	 */
	protected void clearTable(String tableName) throws SQLException, ClassNotFoundException
	{
		String query = "TRUNCATE TABLE " + tableName + ";";
		doUpdate(query);	
	}
	
	/**
	 * Creates a PreparedStatement that will be used to insert a row to the DB
	 * @param tableName The table's name 
	 * @param columnsNumber The number of columns in the table
	 * @return a PreparedStatement object that could be used to insert a row
	 * to the table
	 */
	protected PreparedStatement getPreparedAddRow(
			String tableName, int columnsNumber) throws SQLException, ClassNotFoundException
	{
		validateConnection();
		StringBuilder addRowStringBuilder = new StringBuilder("INSERT INTO " + tableName + " VALUES ("); 
		for (int i = 0; i < columnsNumber; i++)
		{
			addRowStringBuilder.append("?");
			if (i + 1 != columnsNumber)
			{
				addRowStringBuilder.append(",");
			}
		}
		addRowStringBuilder.append(")");
		PreparedStatement preStmt = (PreparedStatement) m_Connection.prepareStatement(addRowStringBuilder.toString());
		return preStmt;
	}
	
	/**
	 * creates a Prepared Statement that will execute the following:
	 * "DELETE FROM " + tableName + " WHERE " + ColumnName "=" + ? ";";
	 * 
	 * @param tableName The table's name 
	 * @param ColumnName The column that will be used to find rows
	 * @param value The value that column need to be equal to
	 * @return a PreparedStatement object that could be used to delete a row
	 * from the table
	 */
	protected PreparedStatement getPreparedDeleteRow(
			String tableName, String ColumnName) throws SQLException, ClassNotFoundException
	{
		validateConnection();
		String deleteRowString = "DELETE FROM " + tableName + " WHERE " + ColumnName + "= ? ;"; 
		PreparedStatement preStmt = (PreparedStatement) m_Connection.prepareStatement(deleteRowString);
		return preStmt;
	}
	
	private PreparedStatement	m_CurrentPreparedStatement;
	protected String			m_DbURL;
	protected String			m_UserName;
	protected String 			m_Password;
	protected Connection		m_Connection;
	protected Statement 		m_Statement;
	protected int				m_ConnectionValidationTimeout; // seconds
	
	public class ColumnType
	{
		String 	name;
		String 	sqlTypeName;
		boolean primaryKey = false;
		Integer primaryKeyLength = null;
	};
	
	/**
	 * tries to close a result set
	 * @param rs a result set
	 */
	public static void CloseResultSet(ResultSet rs)
	{
		try
		{
			if (rs != null)
			{
				rs.close();
			}
		}
		catch(Exception e)
		{
			
		}
	}
	
	/**
	 * tries to close a prepared statement
	 * @param stmt a prepared statement
	 */
	public static void ClosePreparedStatement(PreparedStatement stmt)
	{
		try
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
		catch(Exception e)
		{
			
		}
	}
	
	/**
	 * creates a list of rows from a given result set
	 * 
	 * @param rs a result set object
	 * @return  a list of rows from a given result set
	 */
	public static ArrayList<Object[]> getRows(final ResultSet rs) throws SQLException
	{
		final java.sql.ResultSetMetaData rsMeta = rs.getMetaData();
		final int columnCount = rsMeta.getColumnCount();
		ArrayList<Object[]> rows = new ArrayList<Object[]>();
		while (rs.next())
		{
			Object[] arr = new Object[columnCount];
			for (int i = 0; i < columnCount; i++)
			{
				arr[i] = rs.getObject(i + 1);
			}
			rows.add(arr);
		}
		return rows;
	}
	
	/**
	 * prints the result set to the console.
	 * @param query The query that was given to the DB
	 * @param rs a Result set
	 * @return a list of rows from the given result set
	 */
	public static ArrayList<Object[]> printQueryToConsole(String query, ResultSet rs) throws SQLException
	{
		final java.sql.ResultSetMetaData rsMeta = rs.getMetaData();
		final int columnCount = rsMeta.getColumnCount();
		ArrayList<Object[]> rows = new ArrayList<Object[]>();
		while (rs.next())
		{
			Object[] arr = new Object[columnCount];
			for (int i = 0; i < columnCount; i++)
			{
				arr[i] = rs.getObject(i + 1);
				if (arr[i] == null)
					arr[i] = "";
			}
			printSingleRow(arr);
			rows.add(arr);
		}
		return rows;
	}
	
	/**
	 * prints a single row to the console
	 * @param row an array of values that represents the row
	 */
	public static void printSingleRow(Object[] row)
	{
		int n = row.length;
		for (int j = 0; j < n; j++ )
		{
			System.out.print(row[j].toString() + (j + 1 == n ? "\n" : " "));
		}	
	}
	
	
	/**
	 * prints a query to the console
	 * @param query The query that was given to the DB
	 * @param rows an ArrayList that represents the result of the query
	 * @deprecated this method is @deprecated use printQueryToConsole(String query, ResultSet rs)
	 */
	@Deprecated
	public static void printQueryToConsole(
			String query,
			ArrayList<Object[]> rows)
	{
		System.out.println("Query:" + query);
		System.out.println();
		int n = rows.size();
		if (n == 0)
		{
			return;
		}
		int k = rows.get(0).length;
		for (int i = 0; i < n; i++)
		{
			System.out.println(Integer.toString(i) + ")");
			Object[] cur = rows.get(i);
			for (int j = 0; j < k; j++ )
			{
				System.out.print(cur[j].toString() + (j + 1 == k ? "\n" : " "));
			}
		}
	}
	
	/**
	 * prints a query to an XHTML file
	 * @param fileName the XHTML file name
	 * @param query the original query
	 * @param rows an ArrayList that represents the result of the query
	 * @param rsMeta the original meta data from the result set
	 */
	public static void printQueryToXHTMLFile(
			String fileName,
			String query,
			ArrayList<Object[]> rows,
			java.sql.ResultSetMetaData rsMeta) throws SQLException, ClassCastException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException
	{
		 String[] header = new String[rsMeta.getColumnCount()];
		 for (int i = 0; i < header.length; i++)
		 {
			 header[i] = rsMeta.getColumnName(i+1);
		 }
		 XHTMLDocument doc = new XHTMLDocument(QUERY_RESULT_TEMPLATE_XHTML_FILE);
		 doc.setTable("queryResTable", header, rows);
		 doc.setText("queryHeader", query);
		 doc.save(fileName);
	}
	
	/**
	 * prints a query to a Swing-Frame
	 * @param query the original query
	 * @param rsMeta
	 * @param rows an ArrayList that represents the result of the query
	 */
	public static void printQueryToFrame(
			final String query,
			final ArrayList<Object[]> rows,
			final java.sql.ResultSetMetaData rsMeta)
	throws StdErrReporterException, SQLException
	{
		final int columnCount = rsMeta.getColumnCount();
		QueryResultFrame qrFrame = new QueryResultFrame();
		TableModel tm = new TableModel()
		{

			@Override
			public void addTableModelListener(TableModelListener l)
			{
				
			}

			@Override
			public Class<?> getColumnClass(int columnIndex)
			{
				return getValueAt(0, columnIndex).getClass();
			}

			@Override
			public int getColumnCount()
			{
				return columnCount;
			}

			@Override
			public String getColumnName(int columnIndex)
			{
				try
				{
					return rsMeta.getColumnName(columnIndex + 1);
				} 
				catch (SQLException e)
				{
					
				}
				return "N/A";
			}

			@Override
			public int getRowCount()
			{
				return rows.size();
			}

			@Override
			public Object getValueAt(int rowIndex, int columnIndex)
			{
				return rows.get(rowIndex)[columnIndex];
			}

			@Override
			public boolean isCellEditable(int rowIndex, int columnIndex)
			{
				return true;
			}

			@Override
			public void removeTableModelListener(TableModelListener l)
			{
				
			}

			@Override
			public void setValueAt(Object value, int rowIndex, int columnIndex)
			{
			
			}
			
		};
		
		qrFrame.resultTable.setModel(tm);
		qrFrame.queryField.setText(query);
		qrFrame.invalidate();
		qrFrame.setVisible(true);
		
	}
	public static final String QUERY_RESULT_TEMPLATE_XHTML_FILE = "./data/xhtml/queryRes.xhtml"; 
	public static final String SQL_ERROR = "SQL error: ";
	public static final String SQL_ERROR_CONNECTION_CLOSED = "SQL error: Connection closed.";

}
